#!/usr/bin/python
# @Time    : 2022/11/11 16:58
# @Email   : fengyudeleishui@163.com
# @Author  : Tina Yu
import datetime
import time

import pymysql

from dataMakeCenter.lingzhu.tools.get_run_time import get_run_time

if __name__ == '__main__':

    conn = pymysql.connect(
        host='dbtest2.xhdev.xyz',
        port=3306,
        database='promotion',
        user='xhtest',
        passwd='xh_test',
        charset='utf8'
    )
    cursor = conn.cursor()
    # 统计需要处理的数据量
    cursor.execute(
        "select count(id) from promotion.t_promotion_task_customer "
        "WHERE id NOT in (SELECT cid  FROM promotion.t_promotion_user_sublevel)")
    count = cursor.fetchone()[0]
    print(f'查询到的用户数量为={count}')

    batch_size = 20000  # 每个批次数量
    i = 0  # sublevel表生成id尾数的开始
    times = 1  # 插入次数计数器
    select_cid_sql = 'select id from t_promotion_task_customer WHERE id NOT in' \
                     ' (SELECT cid  FROM t_promotion_user_sublevel) order by id desc limit %s offset %s'

    data_list = []
    start_time = time.time()
    for offset in range(0, count, batch_size):
        if count - offset < batch_size:
            batch_size = count % batch_size
        # 查询一个批次的cid
        with conn.cursor() as cursor:
            cursor.execute(select_cid_sql, (batch_size, offset))
            cid_list_data = cursor.fetchall()
            conn.commit()
        # print(f'查询数量={batch_size};===========跳过的数量={offset}')

        # 逐个拼接插入数据的sql数据
        data_sql = ''
        for cid in cid_list_data:
            cid = cid[0]
            create_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            user_label_time = create_time
            _id = f"{datetime.date.today().strftime('%Y%m%d')}" \
                  f"{'%010d' % (i + 1)}"
            i += 1
            insert_data = f"('{cid}','NOT_LOGIN', '{create_time}', 'Tina', NULL, NULL ,'{user_label_time}', {_id}, '2', NULL)"
            data_sql = data_sql + insert_data + ','
        with conn.cursor() as cursor:
            insert_data_sql = "INSERT INTO promotion.t_promotion_user_sublevel (cid, user_label_type, create_time, creator," \
                              "modify_time, modifier, user_label_time, id, promotion_group_user_label, amount_usage_rate) " \
                              "VALUES"
            sql = insert_data_sql + data_sql[:-1]
            cursor.execute(sql)
            conn.commit()
        print(f'第{times}次插入数据！')
        times += 1

    conn.close()
    end_time = time.time()
    get_run_time(end_time, start_time)
